/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Student;
import com.tces.model.Subject;
import com.tces.model.SubjectOffering;
import com.tces.model.SubjectSchedule;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class SubjectDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(SubjectDAO.class);

    public static List<SubjectSchedule> getInstructorEvaluatedSubjects() throws Exception {
        List<SubjectSchedule> subjectList = new ArrayList<SubjectSchedule>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_code,\n"
                + "s.subject_description,\n"
                + "ss.subject_schedule,\n"
                + "ROUND(Sum(dt.category_mean),2) AS total_mean,\n"
                + "(SELECT rating_qualitative_interpretation FROM ratings WHERE rating_lower_limit <= sum(dt.category_mean) AND rating_upper_limit >= sum(dt.category_mean)  ) AS qualitative_interpretation,\n"
                + "c.college_code,\n"
                + "	concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	) instructor_fullname\n"
                + "FROM\n"
                + "(\n"
                + "		SELECT\n"
                + "	tbl.subject_schedule_id,\n"
                + "	AVG(tbl.question_mean) * ct.category_percentage category_mean\n"
                + "FROM\n"
                + "	(\n"
                + "		SELECT\n"
                + "			ss.subject_schedule_id,\n"
                + "			se.question_id,\n"
                + "			SUM(c.choice_numeric_value) / (\n"
                + "				SELECT\n"
                + "					COUNT(\n"
                + "						DISTINCT subject_students.student_id\n"
                + "					)\n"
                + "				FROM\n"
                + "					subject_students\n"
                + "				WHERE\n"
                + "					subject_students.subject_schedule_id = se.subject_schedule_id\n"
                + "			) question_mean,\n"
                + "			cq.category_id\n"
                + "		FROM\n"
                + "			subject_evaluations AS se\n"
                + "		INNER JOIN choices AS c ON c.choice_id = se.choice_id\n"
                + "		INNER JOIN subject_schedules AS ss ON se.subject_schedule_id = ss.subject_schedule_id\n"
                + "		INNER JOIN categories_questions AS cq ON se.question_id = cq.question_id\n"
                + "		GROUP BY\n"
                + "			ss.subject_schedule_id,\n"
                + "			se.question_id\n"
                + "	) tbl\n"
                + "INNER JOIN categories ct ON tbl.category_id = ct.category_id\n"
                + "GROUP BY\n"
                + "	tbl.subject_schedule_id,\n"
                + "	ct.category_id\n"
                + "	) AS dt\n"
                + "INNER JOIN subject_schedules AS ss ON ss.subject_schedule_id = dt.subject_schedule_id\n"
                + "INNER JOIN instructors AS i ON ss.instructor_id = i.instructor_id\n"
                + "INNER JOIN subjects AS s ON s.subject_id = ss.subject_id\n"
                + "INNER JOIN subject_offerings AS so ON ss.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "GROUP BY\n"
                + "s.subject_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
//        preparedStatement.setString(1, instructorIDNumber);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectSchedule subjectSchedule;
        while (rs.next()) {
            subjectSchedule = new SubjectSchedule();
            subjectSchedule.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectSchedule.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectSchedule.setTotalMean(rs.getDouble("total_mean"));
            subjectSchedule.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectSchedule.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectSchedule.setSubjectCode(rs.getString("subject_code"));
            subjectSchedule.setSubjectDescription(rs.getString("subject_description"));
            subjectSchedule.setQualitativeInterpretation(rs.getString("qualitative_interpretation"));
            subjectSchedule.setSubjectOfferingFullName(rs.getString("college_code")+" - "+rs.getString("subject_offering_name"));
            subjectSchedule.setInstructorFullName(rs.getString("instructor_fullname"));
            subjectList.add(subjectSchedule);
        }
        closeConnection(getConnectionObject());
        return subjectList;
    }

    public static List<SubjectSchedule> getInstructorEvaluatedSubjects(String instructorIDNumber) throws Exception {
        List<SubjectSchedule> subjectList = new ArrayList<SubjectSchedule>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_code,\n"
                + "s.subject_description,\n"
                + "ss.subject_schedule,\n"
                + "ROUND(Sum(dt.category_mean),2) AS total_mean,\n"
                + "(SELECT rating_qualitative_interpretation FROM ratings WHERE rating_lower_limit <= sum(dt.category_mean) AND rating_upper_limit >= sum(dt.category_mean)  ) AS qualitative_interpretation,\n"
                + "c.college_code,\n"
                + "	concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	) instructor_fullname\n"
                + "FROM\n"
                + "(\n"
                + "		SELECT\n"
                + "	tbl.subject_schedule_id,\n"
                + "	AVG(tbl.question_mean) * ct.category_percentage category_mean\n"
                + "FROM\n"
                + "	(\n"
                + "		SELECT\n"
                + "			ss.subject_schedule_id,\n"
                + "			se.question_id,\n"
                + "			SUM(c.choice_numeric_value) / (\n"
                + "				SELECT\n"
                + "					COUNT(\n"
                + "						DISTINCT subject_students.student_id\n"
                + "					)\n"
                + "				FROM\n"
                + "					subject_students\n"
                + "				WHERE\n"
                + "					subject_students.subject_schedule_id = se.subject_schedule_id\n"
                + "			) question_mean,\n"
                + "			cq.category_id\n"
                + "		FROM\n"
                + "			subject_evaluations AS se\n"
                + "		INNER JOIN choices AS c ON c.choice_id = se.choice_id\n"
                + "		INNER JOIN subject_schedules AS ss ON se.subject_schedule_id = ss.subject_schedule_id\n"
                + "		INNER JOIN categories_questions AS cq ON se.question_id = cq.question_id\n"
                + "		GROUP BY\n"
                + "			ss.subject_schedule_id,\n"
                + "			se.question_id\n"
                + "	) tbl\n"
                + "INNER JOIN categories ct ON tbl.category_id = ct.category_id\n"
                + "GROUP BY\n"
                + "	tbl.subject_schedule_id,\n"
                + "	ct.category_id\n"
                + "	) AS dt\n"
                + "INNER JOIN subject_schedules AS ss ON ss.subject_schedule_id = dt.subject_schedule_id\n"
                + "INNER JOIN instructors AS i ON ss.instructor_id = i.instructor_id\n"
                + "INNER JOIN subjects AS s ON s.subject_id = ss.subject_id\n"
                + "INNER JOIN subject_offerings AS so ON ss.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "WHERE i.instructor_idno=?\n"
                + "GROUP BY\n"
                + "s.subject_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setString(1, instructorIDNumber);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectSchedule subjectSchedule;
        while (rs.next()) {
            subjectSchedule = new SubjectSchedule();
             subjectSchedule.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectSchedule.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectSchedule.setTotalMean(rs.getDouble("total_mean"));
            subjectSchedule.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectSchedule.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectSchedule.setSubjectCode(rs.getString("subject_code"));
            subjectSchedule.setSubjectDescription(rs.getString("subject_description"));
            subjectSchedule.setQualitativeInterpretation(rs.getString("qualitative_interpretation"));
            subjectSchedule.setSubjectOfferingFullName(rs.getString("college_code")+" - "+rs.getString("subject_offering_name"));
            subjectSchedule.setInstructorFullName(rs.getString("instructor_fullname"));
            subjectList.add(subjectSchedule);
        }
        closeConnection(getConnectionObject());
        return subjectList;
    }

    /**
     * This will retrieve all the Student's subject open for evaluation.
     *
     * @param studentID
     * @return list of Student's subject
     * @throws Exception
     */
    public static List<SubjectSchedule> getSubjectsForEvaluation(int studentID) throws Exception {
        List<SubjectSchedule> subjectList = new ArrayList<SubjectSchedule>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_schedule,\n"
                + "ss.schedule_status,\n"
                + "ss.subject_code,\n"
                + "s.subject_id,\n"
                + "s.subject_description,\n"
                + "s.subject_status,\n"
                + "s.subject_lec_units,\n"
                + "s.subject_lab_units,\n"
                + "s.subject_type,\n"
                + "i.instructor_id,\n"
                + "i.instructor_first_name,\n"
                + "i.instructor_middle_name,\n"
                + "i.instructor_last_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN subject_schedules AS ss ON ss.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN subjects AS s ON s.subject_id = ss.subject_id\n"
                + "INNER JOIN instructors AS i ON i.instructor_id = ss.instructor_id\n"
                + "INNER JOIN subject_students AS sst ON sst.subject_schedule_id = ss.subject_schedule_id\n"
                + "LEFT JOIN subject_evaluations se ON ss.subject_schedule_id = se.subject_schedule_id\n"
                + "WHERE i.instructor_visible=1 \n"
                + "AND ss.schedule_status=1\n"
                + "AND sst.student_id=?\n"
                + "AND se.subject_schedule_id is null";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, studentID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectSchedule subjectSchedule;
        while (rs.next()) {
            subjectSchedule = new SubjectSchedule();
            subjectSchedule.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectSchedule.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectSchedule.setScheduleStatus(rs.getInt("schedule_status"));
            subjectSchedule.setSubjectID(rs.getInt("subject_id"));
            subjectSchedule.setSubjectType(rs.getInt("subject_type"));
            subjectSchedule.setSubjectStatus(rs.getInt("subject_status"));
            subjectSchedule.setInstructorID(rs.getInt("instructor_id"));
            subjectSchedule.setSubjectLecUnits(rs.getDouble("subject_lec_units"));
            subjectSchedule.setSubjectLabUnits(rs.getDouble("subject_lab_units"));
            subjectSchedule.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectSchedule.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectSchedule.setSubjectCode(rs.getString("subject_code"));
            subjectSchedule.setSubjectDescription(rs.getString("subject_description"));
            subjectSchedule.setInstructorFirstName(rs.getString("instructor_first_name"));
            subjectSchedule.setInstructorMiddleName(rs.getString("instructor_middle_name"));
            subjectSchedule.setInstructorLastName(rs.getString("instructor_last_name"));
            subjectList.add(subjectSchedule);
        }
        closeConnection(getConnectionObject());
        return subjectList;
    }

    /**
     * This method will check if the student evaluated the subject already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @return number of duplicate
     */
    public static int checkDuplicate(int subjectScheduleID, int studentID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    /**
     * This method will check if the student evaluated the subject and specific
     * question already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @return number of duplicate
     */
    /**
     * This method will check if the student evaluated the subject and specific
     * question already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @param questionID - Question ID
     * @return number of duplicate
     */
    public static int checkDuplicate(int subjectScheduleID, int studentID, int questionID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=? AND question_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            preparedStatementID.setInt(3, questionID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    /**
     * Save the evaluation details.If there's an existing record for that
     * subject schedule id and student id it will be deleted
     *
     * @param subjectScheduleID
     * @param studentID
     * @param questionID
     * @param choiceID
     * @return 1 for successful insert 0 for errors
     */
    public static int saveEvaluation(int subjectScheduleID, int studentID, int questionID, int choiceID) throws Exception {
        int result = 0;
        try {
            if (checkDuplicate(subjectScheduleID, studentID, questionID) > 0) {
                deleteEvaluation(subjectScheduleID, studentID);
                throw new Exception("Saving duplicate Question ID is not allowed. Please check the form.");
            }
            if (validateQuestionChoiceID(questionID, choiceID) <= 0) {
                deleteEvaluation(subjectScheduleID, studentID);
                throw new Exception("Invalid choice ID. Please check the form.");
            }
            String sql = "INSERT INTO subject_evaluations(subject_schedule_id, student_id,question_id,choice_id) values(?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            preparedStatement.setInt(3, questionID);
            preparedStatement.setInt(4, choiceID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        return result;
    }

    /**
     * Delete all row that with
     *
     * @param subjectScheduleID and
     * @param studentID
     * @param subjectScheduleID
     * @param studentID
     * @return
     */
    public static int deleteEvaluation(int subjectScheduleID, int studentID) {
        int result = 0;
        try {
            String sql = "DELETE FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            log.error(ex);
        }
        return result;
    }

    public static int saveEvaluationSuggestion(int subjectScheduleID, int studentID, String suggestion) throws Exception {
        int result = 0;
        try {
            String sql = "INSERT INTO subject_evaluation_suggestions(subject_schedule_id, student_id,subject_evaluation_suggestion) values(?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            preparedStatement.setString(3, suggestion);
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        log.info(result);
        return result;
    }

    public static int deleteEvaluationSuggestion(int subjectScheduleID, int studentID) {
        int result = 0;
        try {
            String sql = "DELETE FROM subject_evaluation_suggestions WHERE subject_schedule_id=? AND student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        log.info(result);
        return result;
    }

    public static int checkDuplicateSuggestion(int subjectScheduleID, int studentID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluation_suggestions WHERE subject_schedule_id=? AND student_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    public static int validateQuestionChoiceID(int questionID, int choiceID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM questions_choices WHERE question_id=? AND choice_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, questionID);
            preparedStatementID.setInt(2, choiceID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    public static List<Subject> getSubjects() {
        List<Subject> subjectList = new ArrayList<Subject>();
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT * FROM subjects ORDER BY subject_name";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();

            Subject subject;
            while (rs.next()) {
                subject = new Subject();
                subject.setSubjectID(rs.getInt("subject_id"));
                subject.setSubjectName(rs.getString("subject_name"));
                subject.setSubjectDescription(rs.getString("subject_description"));
                subject.setSubjectStatus(rs.getInt("subject_status"));
                subject.setSubjectType(rs.getInt("subject_type"));
                subject.setSubjectLecUnits(rs.getDouble("subject_lec_units"));
                subject.setSubjectLabUnits(rs.getDouble("subject_lab_units"));
                subjectList.add(subject);
            }
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return subjectList;
    }

    public static int add(Subject subject) throws Exception {
        try {
            String sql = "INSERT INTO subjects(subject_name,subject_description,subject_lec_units,subject_lab_units,subject_type, subject_status) values(?,?,?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, subject.getSubjectName());
            preparedStatement.setString(2, subject.getSubjectDescription());
            preparedStatement.setDouble(3, subject.getSubjectLecUnits());
            preparedStatement.setDouble(4, subject.getSubjectLabUnits());
            preparedStatement.setInt(5, subject.getSubjectType());
            preparedStatement.setInt(6, subject.getSubjectStatus());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int update(Subject subject) throws Exception {
        try {
            String sql = "UPDATE subjects SET subject_name=?,subject_description=?,subject_status=?,subject_lec_units=?,subject_lab_units=?,subject_type=? WHERE subject_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, subject.getSubjectName());
            preparedStatement.setString(2, subject.getSubjectDescription());
            preparedStatement.setInt(3, subject.getSubjectStatus());
            preparedStatement.setDouble(4, subject.getSubjectLecUnits());
            preparedStatement.setDouble(5, subject.getSubjectLabUnits());
            preparedStatement.setInt(6, subject.getSubjectType());
            preparedStatement.setInt(7, subject.getSubjectID());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int delete(int subjectID) throws Exception {
        try {
            String sql = "UPDATE subjects set subject_status=0 WHERE subject_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static Subject find(int subjectID) throws Exception {
        PreparedStatement preparedStatementID;
        Subject subject = new Subject();
        try {
            String sql = "SELECT * FROM subjects WHERE subject_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                subject.setSubjectID(rs.getInt("subject_id"));
                subject.setSubjectName(rs.getString("subject_name"));
                subject.setSubjectDescription(rs.getString("subject_description"));
                subject.setSubjectStatus(rs.getInt("subject_status"));
                subject.setSubjectType(rs.getInt("subject_type"));
                subject.setSubjectLecUnits(rs.getDouble("subject_lec_units"));
                subject.setSubjectLabUnits(rs.getDouble("subject_lab_units"));
            }
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return subject;
    }
}
